﻿using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using MxWeiXinPF.DBUtility;
using MxWeiXinPF.Common;
using System.Collections.Generic;//Please add references
namespace MxWeiXinPF.DAL
{
	/// <summary>
	/// 数据访问类:wx_shop_category
	/// </summary>
    public partial class wx_shop_category
    {
        public int Add(MxWeiXinPF.Model.wx_shop_category model)
        {
            using (SqlConnection connection = new SqlConnection(DbHelperSQL.connectionString))
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        StringBuilder builder = new StringBuilder();
                        builder.Append("insert into wx_shop_category(");
                        builder.Append("title,parent_id,class_list,class_layer,sort_id,link_url,img_url,class_content,seo_title,seo_keywords,seo_description,wid,index_ico_url,ico_url)");
                        builder.Append(" values (");
                        builder.Append("@title,@parent_id,@class_list,@class_layer,@sort_id,@link_url,@img_url,@class_content,@seo_title,@seo_keywords,@seo_description,@wid,@index_ico_url,@ico_url)");
                        builder.Append(";select @@IDENTITY");
                        SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@title", SqlDbType.NVarChar, 100), new SqlParameter("@parent_id", SqlDbType.Int, 4), 
                            new SqlParameter("@class_list", SqlDbType.NVarChar, 500), new SqlParameter("@class_layer", SqlDbType.Int, 4), 
                            new SqlParameter("@sort_id", SqlDbType.Int, 4), new SqlParameter("@link_url", SqlDbType.NVarChar, 0xff), 
                            new SqlParameter("@img_url", SqlDbType.NVarChar, 0xff), new SqlParameter("@class_content", SqlDbType.NText), 
                            new SqlParameter("@seo_title", SqlDbType.NVarChar, 0xff), new SqlParameter("@seo_keywords", SqlDbType.NVarChar, 0xff), 
                            new SqlParameter("@seo_description", SqlDbType.NVarChar, 0xff), new SqlParameter("@wid", SqlDbType.Int, 4), 
                            new SqlParameter("@index_ico_url", SqlDbType.NVarChar, 1000),new SqlParameter("@ico_url", SqlDbType.NVarChar, 500) };
                        cmdParms[0].Value = model.title;
                        cmdParms[1].Value = model.parent_id;
                        cmdParms[2].Value = model.class_list;
                        cmdParms[3].Value = model.class_layer;
                        cmdParms[4].Value = model.sort_id;
                        cmdParms[5].Value = model.link_url;
                        cmdParms[6].Value = model.img_url;
                        cmdParms[7].Value = model.class_content;
                        cmdParms[8].Value = model.seo_title;
                        cmdParms[9].Value = model.seo_keywords;
                        cmdParms[10].Value = model.seo_description;
                        cmdParms[11].Value = model.wid;
                        cmdParms[12].Value = model.index_ico_url;
                        cmdParms[13].Value = model.ico_url;
                        object obj2 = DbHelperSQL.GetSingle(connection, transaction, builder.ToString(), cmdParms);
                        model.id = Convert.ToInt32(obj2);
                        if (model.parent_id > 0)
                        {
                            MxWeiXinPF.Model.wx_shop_category _category = this.GetModel(connection, transaction, model.parent_id.Value);
                            model.class_list = _category.class_list + model.id + ",";
                            model.class_layer = _category.class_layer + 1;
                        }
                        else
                        {
                            model.class_list = "," + model.id + ",";
                            model.class_layer = 1;
                        }
                        DbHelperSQL.ExecuteSql(connection, transaction, string.Concat(new object[] { "update wx_shop_category set class_list='", model.class_list, "', class_layer=", model.class_layer, " where id=", model.id }));
                        transaction.Commit();
                    }
                    catch
                    {
                        transaction.Rollback();
                        return 0;
                    }
                }
            }
            return model.id;
        }

        public MxWeiXinPF.Model.wx_shop_category DataRowToModel(DataRow row)
        {
            MxWeiXinPF.Model.wx_shop_category _category = new MxWeiXinPF.Model.wx_shop_category();
            if (row != null)
            {
                if ((row["id"] != null) && (row["id"].ToString() != ""))
                {
                    _category.id = int.Parse(row["id"].ToString());
                }
                if (row["title"] != null)
                {
                    _category.title = row["title"].ToString();
                }
                if (row["code"] != null)
                {
                    _category.code = row["code"].ToString();
                }
                if ((row["parent_id"] != null) && (row["parent_id"].ToString() != ""))
                {
                    _category.parent_id = new int?(int.Parse(row["parent_id"].ToString()));
                }
                if (row["class_list"] != null)
                {
                    _category.class_list = row["class_list"].ToString();
                }
                if ((row["class_layer"] != null) && (row["class_layer"].ToString() != ""))
                {
                    _category.class_layer = new int?(int.Parse(row["class_layer"].ToString()));
                }
                if ((row["sort_id"] != null) && (row["sort_id"].ToString() != ""))
                {
                    _category.sort_id = new int?(int.Parse(row["sort_id"].ToString()));
                }
                if (row["link_url"] != null)
                {
                    _category.link_url = row["link_url"].ToString();
                }
                if (row["img_url"] != null)
                {
                    _category.img_url = row["img_url"].ToString();
                }
                if (row["class_content"] != null)
                {
                    _category.class_content = row["class_content"].ToString();
                }
                if (row["remark"] != null)
                {
                    _category.remark = row["remark"].ToString();
                }
                if (row["seo_title"] != null)
                {
                    _category.seo_title = row["seo_title"].ToString();
                }
                if (row["seo_keywords"] != null)
                {
                    _category.seo_keywords = row["seo_keywords"].ToString();
                }
                if (row["seo_description"] != null)
                {
                    _category.seo_description = row["seo_description"].ToString();
                }
                if ((row["wid"] != null) && (row["wid"].ToString() != ""))
                {
                    _category.wid = new int?(int.Parse(row["wid"].ToString()));
                }
                if (row["ico_url"] != null)
                {
                    _category.ico_url = row["ico_url"].ToString();
                }
                if (row["index_ico_url"] != null)
                {
                    _category.index_ico_url = row["index_ico_url"].ToString();
                }
            }
            return _category;
        }

        public bool DeleteCategory(int id)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("delete from wx_shop_category ");
            builder.Append(" where id=@id and   id not in(select categoryId  from wx_shop_product) ");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@id", SqlDbType.Int, 4) };
            cmdParms[0].Value = id;
            return (DbHelperSQL.ExecuteSql(builder.ToString(), cmdParms) > 0);
        }

        public bool DeleteList(string idlist)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("delete from wx_shop_category ");
            builder.Append(" where id in (" + idlist + ")  ");
            return (DbHelperSQL.ExecuteSql(builder.ToString()) > 0);
        }

        public bool Exists(int id)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select count(1) from wx_shop_category");
            builder.Append(" where id=@id");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@id", SqlDbType.Int, 4) };
            cmdParms[0].Value = id;
            return DbHelperSQL.Exists(builder.ToString(), cmdParms);
        }

        public MxWeiXinPF.Model.wx_shop_category GetCategoryByWid(int wid, int id)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select top 1");
            builder.Append(" id,title,code,parent_id,class_list,class_layer,sort_id,link_url,img_url,class_content,remark,seo_title,seo_keywords,seo_description,wid,index_ico_url,ico_url  ");
            builder.Append(" from wx_shop_category  where   id=@id  and wid=@wid");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@id", SqlDbType.Int, 4), new SqlParameter("@wid", SqlDbType.Int, 4) };
            cmdParms[0].Value = id;
            cmdParms[1].Value = wid;
            SqlDataReader reader = DbHelperSQL.ExecuteReader(builder.ToString(), cmdParms);
            MxWeiXinPF.Model.wx_shop_category _category = new MxWeiXinPF.Model.wx_shop_category();
            while (reader.Read())
            {
                _category = new MxWeiXinPF.Model.wx_shop_category
                {
                    id = MyCommFun.Obj2Int(reader["id"]),
                    title = MyCommFun.ObjToStr(reader["title"]),
                    parent_id = new int?(MyCommFun.Obj2Int(reader["parent_id"])),
                    class_layer = new int?(MyCommFun.Obj2Int(reader["class_layer"])),
                    link_url = MyCommFun.ObjToStr(reader["link_url"]),
                    img_url = MyCommFun.ObjToStr(reader["img_url"]),
                    class_content = MyCommFun.ObjToStr(reader["class_content"]),
                    ico_url = MyCommFun.ObjToStr(reader["ico_url"]),
                    index_ico_url = MyCommFun.ObjToStr(reader["index_ico_url"]),
                    seo_title = MyCommFun.ObjToStr(reader["seo_title"]),
                    seo_keywords = MyCommFun.ObjToStr(reader["seo_keywords"]),
                    seo_description = MyCommFun.ObjToStr(reader["seo_description"])
                };
            }
            reader.Close();
            return _category;
        }

        public IList<MxWeiXinPF.Model.wx_shop_category> GetCategoryListByWid(int wid, int topNum, int parentId, int class_layer)
        {
            IList<MxWeiXinPF.Model.wx_shop_category> list = new List<MxWeiXinPF.Model.wx_shop_category>();
            StringBuilder builder = new StringBuilder();
            builder.Append("select ");
            if (topNum >= 0)
            {
                builder.Append(" top " + topNum + " ");
            }
            builder.Append(" id,title,code,parent_id,class_list,class_layer,sort_id,link_url,img_url,class_content,remark,seo_title,seo_keywords,seo_description,wid,index_ico_url,ico_url,(select COUNT(1) from wx_shop_product where categoryId=wx_shop_category.id) isproductnum  from wx_shop_category   where 1=1 ");
            if (parentId != -1)
            {
                builder.Append(" and parent_id=" + parentId);
            }
            if (class_layer != -1)
            {
                builder.Append(" and class_layer=" + class_layer);
            }
            builder.Append(" and wid=@wid order by sort_id asc");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@wid", SqlDbType.Int, 4) };
            cmdParms[0].Value = wid;
            SqlDataReader reader = DbHelperSQL.ExecuteReader(builder.ToString(), cmdParms);
            MxWeiXinPF.Model.wx_shop_category item = new MxWeiXinPF.Model.wx_shop_category();
            while (reader.Read())
            {
                item = new MxWeiXinPF.Model.wx_shop_category
                {
                    id = MyCommFun.Obj2Int(reader["id"]),
                    title = MyCommFun.ObjToStr(reader["title"]),
                    parent_id = new int?(MyCommFun.Obj2Int(reader["parent_id"])),
                    class_layer = new int?(MyCommFun.Obj2Int(reader["class_layer"])),
                    link_url = MyCommFun.ObjToStr(reader["link_url"]),
                    img_url = MyCommFun.ObjToStr(reader["img_url"]),
                    class_content = MyCommFun.ObjToStr(reader["class_content"]),
                    ico_url = MyCommFun.ObjToStr(reader["ico_url"]),
                    index_ico_url = MyCommFun.ObjToStr(reader["index_ico_url"]),
                    seo_title = MyCommFun.ObjToStr(reader["seo_title"]),
                    seo_keywords = MyCommFun.ObjToStr(reader["seo_keywords"]),
                    seo_description = MyCommFun.ObjToStr(reader["seo_description"]),
                    isproductnum = MyCommFun.Obj2Int(reader["isproductnum"])
                };
                list.Add(item);
            }
            reader.Close();
            return list;
        }

        public IList<MxWeiXinPF.Model.wx_shop_category> GetCategoryListByWidShopid(int wid, int shopid, int topNum, int parentId, int class_layer)
        {
            IList<MxWeiXinPF.Model.wx_shop_category> list = new List<MxWeiXinPF.Model.wx_shop_category>();
            StringBuilder builder = new StringBuilder();
            builder.Append("select ");
            if (topNum >= 0)
            {
                builder.Append(" top " + topNum + " ");
            }
            builder.Append(" a.*,b.img_url as catepicurl,b.isview ");
            builder.Append(" ,(select COUNT(1) from wx_shop_product where categoryId=a.id and shopid=" + shopid + ") isproductnum ");
            builder.Append("  from wx_shop_category a  ");
            builder.Append(" left join (select categoryid,img_url,link_url,isview from dt_indexcatepic where shopid=" + shopid + ") as b on a.id=b.categoryid ");
            builder.Append("  where 1=1 ");
            if (parentId != -1)
            {
                builder.Append(" and a.parent_id=" + parentId);
            }
            if (class_layer != -1)
            {
                builder.Append(" and a.class_layer=" + class_layer);
            }
            builder.Append(" and a.wid=@wid order by sort_id asc");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@wid", SqlDbType.Int, 4) };
            cmdParms[0].Value = wid;
            SqlDataReader reader = DbHelperSQL.ExecuteReader(builder.ToString(), cmdParms);
            MxWeiXinPF.Model.wx_shop_category item = new MxWeiXinPF.Model.wx_shop_category();
            while (reader.Read())
            {
                item = new MxWeiXinPF.Model.wx_shop_category
                {
                    id = MyCommFun.Obj2Int(reader["id"]),
                    title = MyCommFun.ObjToStr(reader["title"]),
                    parent_id = new int?(MyCommFun.Obj2Int(reader["parent_id"])),
                    class_layer = new int?(MyCommFun.Obj2Int(reader["class_layer"])),
                    link_url = MyCommFun.ObjToStr(reader["link_url"]),
                    img_url = MyCommFun.ObjToStr(reader["img_url"]),
                    class_content = MyCommFun.ObjToStr(reader["class_content"]),
                    ico_url = MyCommFun.ObjToStr(reader["ico_url"]),
                    index_ico_url = MyCommFun.ObjToStr(reader["index_ico_url"]),
                    seo_title = MyCommFun.ObjToStr(reader["seo_title"]),
                    seo_keywords = MyCommFun.ObjToStr(reader["seo_keywords"]),
                    seo_description = MyCommFun.ObjToStr(reader["seo_description"]),
                    isproductnum = MyCommFun.Obj2Int(reader["isproductnum"]),
                    catepicurl = MyCommFun.ObjToStr(reader["catepicurl"]),
                    isview = MyCommFun.ObjToStr(reader["isview"])
                };
                list.Add(item);
            }
            reader.Close();
            return list;
        }

        private void GetChilds(DataTable oldData, DataTable newData, int parent_id)
        {
            DataRow[] rowArray = oldData.Select("parent_id=" + parent_id);
            for (int i = 0; i < rowArray.Length; i++)
            {
                DataRow row = newData.NewRow();
                row["id"] = int.Parse(rowArray[i]["id"].ToString());
                row["title"] = rowArray[i]["title"].ToString();
                row["remark"] = rowArray[i]["remark"].ToString();
                row["parent_id"] = int.Parse(rowArray[i]["parent_id"].ToString());
                row["class_list"] = rowArray[i]["class_list"].ToString();
                row["class_layer"] = int.Parse(rowArray[i]["class_layer"].ToString());
                row["sort_id"] = int.Parse(rowArray[i]["sort_id"].ToString());
                row["link_url"] = rowArray[i]["link_url"].ToString();
                row["img_url"] = rowArray[i]["img_url"].ToString();
                row["class_content"] = rowArray[i]["class_content"].ToString();
                row["seo_title"] = rowArray[i]["seo_title"].ToString();
                row["seo_keywords"] = rowArray[i]["seo_keywords"].ToString();
                row["seo_description"] = rowArray[i]["seo_description"].ToString();
                row["ico_url"] = rowArray[i]["ico_url"].ToString();
                row["index_ico_url"] = rowArray[i]["index_ico_url"].ToString();
                row["wid"] = rowArray[i]["wid"].ToString();
                newData.Rows.Add(row);
                this.GetChilds(oldData, newData, int.Parse(rowArray[i]["id"].ToString()));
            }
        }

        public DataSet GetList(string strWhere)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select id,title,code,parent_id,class_list,class_layer,sort_id,link_url,img_url,class_content,remark,seo_title,seo_keywords,seo_description,wid,index_ico_url,ico_url ");
            builder.Append(" FROM wx_shop_category ");
            if (strWhere.Trim() != "")
            {
                builder.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(builder.ToString());
        }

        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select ");
            if (Top > 0)
            {
                builder.Append(" top " + Top.ToString());
            }
            builder.Append(" id,title,code,parent_id,class_list,class_layer,sort_id,link_url,img_url,class_content,remark,seo_title,seo_keywords,seo_description,wid,index_ico_url,ico_url ");
            builder.Append(" FROM wx_shop_category ");
            if (strWhere.Trim() != "")
            {
                builder.Append(" where " + strWhere);
            }
            builder.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(builder.ToString());
        }

        public DataSet GetList(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append(" select  * from wx_shop_category ");
            if (strWhere.Trim() != "")
            {
                builder.Append(" where  " + strWhere);
            }
            recordCount = Convert.ToInt32(DbHelperSQL.GetSingle(PagingHelper.CreateCountingSql(builder.ToString())));
            return DbHelperSQL.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, builder.ToString(), filedOrder));
        }

        public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("SELECT * FROM ( ");
            builder.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                builder.Append("order by T." + orderby);
            }
            else
            {
                builder.Append("order by T.id desc");
            }
            builder.Append(")AS Row, T.*  from wx_shop_category T ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                builder.Append(" WHERE " + strWhere);
            }
            builder.Append(" ) TT");
            builder.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
            return DbHelperSQL.Query(builder.ToString());
        }

        public int GetMaxId()
        {
            return DbHelperSQL.GetMaxID("id", "wx_shop_category");
        }

        public MxWeiXinPF.Model.wx_shop_category GetModel(int id)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select  top 1 id,title,code,parent_id,class_list,class_layer,sort_id,link_url,img_url,class_content,remark,seo_title,seo_keywords,seo_description,wid,index_ico_url,ico_url from wx_shop_category ");
            builder.Append(" where id=@id");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@id", SqlDbType.Int, 4) };
            cmdParms[0].Value = id;
            MxWeiXinPF.Model.wx_shop_category _category = new MxWeiXinPF.Model.wx_shop_category();
            DataSet set = DbHelperSQL.Query(builder.ToString(), cmdParms);
            if (set.Tables[0].Rows.Count > 0)
            {
                return this.DataRowToModel(set.Tables[0].Rows[0]);
            }
            return null;
        }

        public MxWeiXinPF.Model.wx_shop_category GetModel(SqlConnection conn, SqlTransaction trans, int id)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select  top 1  id,title,code,parent_id,class_list,class_layer,sort_id,link_url,img_url,class_content,remark,seo_title,seo_keywords,seo_description,wid,index_ico_url,ico_url from wx_shop_category  ");
            builder.Append(" where id=@id");
            SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@id", SqlDbType.Int, 4) };
            cmdParms[0].Value = id;
            MxWeiXinPF.Model.wx_shop_category _category = new MxWeiXinPF.Model.wx_shop_category();
            DataSet set = DbHelperSQL.Query(conn, trans, builder.ToString(), cmdParms);
            if (set.Tables[0].Rows.Count > 0)
            {
                if ((set.Tables[0].Rows[0]["id"] != null) && (set.Tables[0].Rows[0]["id"].ToString() != ""))
                {
                    _category.id = int.Parse(set.Tables[0].Rows[0]["id"].ToString());
                }
                if ((set.Tables[0].Rows[0]["title"] != null) && (set.Tables[0].Rows[0]["title"].ToString() != ""))
                {
                    _category.title = set.Tables[0].Rows[0]["title"].ToString();
                }
                if ((set.Tables[0].Rows[0]["parent_id"] != null) && (set.Tables[0].Rows[0]["parent_id"].ToString() != ""))
                {
                    _category.parent_id = new int?(int.Parse(set.Tables[0].Rows[0]["parent_id"].ToString()));
                }
                if ((set.Tables[0].Rows[0]["class_list"] != null) && (set.Tables[0].Rows[0]["class_list"].ToString() != ""))
                {
                    _category.class_list = set.Tables[0].Rows[0]["class_list"].ToString();
                }
                if ((set.Tables[0].Rows[0]["class_layer"] != null) && (set.Tables[0].Rows[0]["class_layer"].ToString() != ""))
                {
                    _category.class_layer = new int?(int.Parse(set.Tables[0].Rows[0]["class_layer"].ToString()));
                }
                if ((set.Tables[0].Rows[0]["sort_id"] != null) && (set.Tables[0].Rows[0]["sort_id"].ToString() != ""))
                {
                    _category.sort_id = new int?(int.Parse(set.Tables[0].Rows[0]["sort_id"].ToString()));
                }
                if ((set.Tables[0].Rows[0]["link_url"] != null) && (set.Tables[0].Rows[0]["link_url"].ToString() != ""))
                {
                    _category.link_url = set.Tables[0].Rows[0]["link_url"].ToString();
                }
                if ((set.Tables[0].Rows[0]["img_url"] != null) && (set.Tables[0].Rows[0]["img_url"].ToString() != ""))
                {
                    _category.img_url = set.Tables[0].Rows[0]["img_url"].ToString();
                }
                if ((set.Tables[0].Rows[0]["class_content"] != null) && (set.Tables[0].Rows[0]["class_content"].ToString() != ""))
                {
                    _category.class_content = set.Tables[0].Rows[0]["class_content"].ToString();
                }
                if ((set.Tables[0].Rows[0]["seo_title"] != null) && (set.Tables[0].Rows[0]["seo_title"].ToString() != ""))
                {
                    _category.seo_title = set.Tables[0].Rows[0]["seo_title"].ToString();
                }
                if ((set.Tables[0].Rows[0]["seo_keywords"] != null) && (set.Tables[0].Rows[0]["seo_keywords"].ToString() != ""))
                {
                    _category.seo_keywords = set.Tables[0].Rows[0]["seo_keywords"].ToString();
                }
                if ((set.Tables[0].Rows[0]["seo_description"] != null) && (set.Tables[0].Rows[0]["seo_description"].ToString() != ""))
                {
                    _category.seo_description = set.Tables[0].Rows[0]["seo_description"].ToString();
                }
                if ((set.Tables[0].Rows[0]["ico_url"] != null) && (set.Tables[0].Rows[0]["ico_url"].ToString() != ""))
                {
                    _category.ico_url = set.Tables[0].Rows[0]["ico_url"].ToString();
                }
                if ((set.Tables[0].Rows[0]["index_ico_url"] != null) && (set.Tables[0].Rows[0]["index_ico_url"].ToString() != ""))
                {
                    _category.index_ico_url = set.Tables[0].Rows[0]["index_ico_url"].ToString();
                }
                if ((set.Tables[0].Rows[0]["wid"] != null) && (set.Tables[0].Rows[0]["wid"].ToString() != ""))
                {
                    _category.wid = new int?(int.Parse(set.Tables[0].Rows[0]["wid"].ToString()));
                }
                return _category;
            }
            return null;
        }

        public string GetName(string categoryid)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select top 1 title from wx_shop_category ");
            builder.Append(" where id=" + categoryid);
            string str = Convert.ToString(DbHelperSQL.GetSingle(builder.ToString()));
            if (string.IsNullOrEmpty(str))
            {
                return "";
            }
            return str;
        }

        public int GetRecordCount(string strWhere)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select count(1) FROM wx_shop_category ");
            if (strWhere.Trim() != "")
            {
                builder.Append(" where " + strWhere);
            }
            object single = DbHelperSQL.GetSingle(builder.ToString());
            if (single == null)
            {
                return 0;
            }
            return Convert.ToInt32(single);
        }

        public DataTable GetWCodeList(int wid, int parent_id)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select   id,title,code,parent_id,class_list,class_layer,sort_id,link_url,img_url,class_content,remark,seo_title,seo_keywords,seo_description,wid,index_ico_url,ico_url from wx_shop_category ");
            builder.Append(" where   wid=" + wid + "  order by sort_id asc,id desc");
            DataTable oldData = DbHelperSQL.Query(builder.ToString()).Tables[0];
            if (oldData == null)
            {
                return null;
            }
            DataTable newData = oldData.Clone();
            this.GetChilds(oldData, newData, parent_id);
            return newData;
        }

        private bool IsContainNode(int id, int parent_id)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("select count(1) from  wx_shop_category ");
            builder.Append(string.Concat(new object[] { " where class_list like '%,", id, ",%' and id=", parent_id }));
            return DbHelperSQL.Exists(builder.ToString());
        }

        public bool Update(MxWeiXinPF.Model.wx_shop_category model)
        {
            using (SqlConnection connection = new SqlConnection(DbHelperSQL.connectionString))
            {
                connection.Open();
                using (SqlTransaction transaction = connection.BeginTransaction())
                {
                    try
                    {
                        if (this.IsContainNode(model.id, model.parent_id.Value))
                        {
                            MxWeiXinPF.Model.wx_shop_category _category = this.GetModel(model.id);
                            string str = "," + model.parent_id + ",";
                            int num = 1;
                            if (_category.parent_id > 0)
                            {
                                MxWeiXinPF.Model.wx_shop_category _category2 = this.GetModel(connection, transaction, _category.parent_id.Value);
                                str = _category2.class_list + model.parent_id + ",";
                                num = _category2.class_layer.Value + 1;
                            }
                            DbHelperSQL.ExecuteSql(connection, transaction, string.Concat(new object[] { "update wx_shop_category set parent_id=", _category.parent_id, 
                                ",class_list='", str, "', class_layer=", num, " where id=", model.parent_id }));
                            this.UpdateChilds(connection, transaction, model.parent_id.Value);
                        }
                        if (model.parent_id > 0)
                        {
                            MxWeiXinPF.Model.wx_shop_category _category3 = this.GetModel(connection, transaction, model.parent_id.Value);
                            model.class_list = _category3.class_list + model.id + ",";
                            model.class_layer = _category3.class_layer + 1;
                        }
                        else
                        {
                            model.class_list = "," + model.id + ",";
                            model.class_layer = 1;
                        }
                        StringBuilder builder = new StringBuilder();
                        builder.Append("update wx_shop_category set ");
                        builder.Append("title=@title,");
                        builder.Append("parent_id=@parent_id,");
                        builder.Append("class_list=@class_list,");
                        builder.Append("class_layer=@class_layer,");
                        builder.Append("sort_id=@sort_id,");
                        builder.Append("link_url=@link_url,");
                        builder.Append("img_url=@img_url,");
                        builder.Append("class_content=@class_content,");
                        builder.Append("seo_title=@seo_title,");
                        builder.Append("seo_keywords=@seo_keywords,");
                        builder.Append("seo_description=@seo_description,");
                        builder.Append("wid=@wid,");
                        builder.Append("index_ico_url=@index_ico_url,");
                        builder.Append("ico_url=@ico_url");
                        builder.Append(" where id=@id");
                        SqlParameter[] cmdParms = new SqlParameter[] { new SqlParameter("@title", SqlDbType.NVarChar, 100), 
                            new SqlParameter("@parent_id", SqlDbType.Int, 4), new SqlParameter("@class_list", SqlDbType.NVarChar, 500), 
                            new SqlParameter("@class_layer", SqlDbType.Int, 4), new SqlParameter("@sort_id", SqlDbType.Int, 4), 
                            new SqlParameter("@link_url", SqlDbType.NVarChar, 0xff), new SqlParameter("@img_url", SqlDbType.NVarChar, 0xff), 
                            new SqlParameter("@class_content", SqlDbType.NText), new SqlParameter("@seo_title", SqlDbType.NVarChar, 0xff), 
                            new SqlParameter("@seo_keywords", SqlDbType.NVarChar, 0xff), new SqlParameter("@seo_description", SqlDbType.NVarChar, 0xff), 
                            new SqlParameter("@wid", SqlDbType.Int, 4),new SqlParameter("@index_ico_url", SqlDbType.NVarChar, 1000),
                            new SqlParameter("@ico_url", SqlDbType.NVarChar, 500), new SqlParameter("@id", SqlDbType.Int, 4) };
                        cmdParms[0].Value = model.title;
                        cmdParms[1].Value = model.parent_id;
                        cmdParms[2].Value = model.class_list;
                        cmdParms[3].Value = model.class_layer;
                        cmdParms[4].Value = model.sort_id;
                        cmdParms[5].Value = model.link_url;
                        cmdParms[6].Value = model.img_url;
                        cmdParms[7].Value = model.class_content;
                        cmdParms[8].Value = model.seo_title;
                        cmdParms[9].Value = model.seo_keywords;
                        cmdParms[10].Value = model.seo_description;
                        cmdParms[11].Value = model.wid;
                        cmdParms[12].Value = model.index_ico_url;
                        cmdParms[13].Value = model.ico_url;
                        cmdParms[14].Value = model.id;
                        DbHelperSQL.ExecuteSql(connection, transaction, builder.ToString(), cmdParms);
                        this.UpdateChilds(connection, transaction, model.id);
                        transaction.Commit();
                    }
                    catch (Exception)
                    {
                        transaction.Rollback();
                        return false;
                    }
                }
            }
            return true;
        }

        private void UpdateChilds(SqlConnection conn, SqlTransaction trans, int parent_id)
        {
            MxWeiXinPF.Model.wx_shop_category _category = this.GetModel(conn, trans, parent_id);
            if (_category != null)
            {
                string sQLString = "select id from wx_shop_category where parent_id=" + parent_id;
                DataSet set = DbHelperSQL.Query(conn, trans, sQLString);
                foreach (DataRow row in set.Tables[0].Rows)
                {
                    int num = int.Parse(row["id"].ToString());
                    string str2 = _category.class_list + num + ",";
                    int num2 = _category.class_layer.Value + 1;
                    DbHelperSQL.ExecuteSql(conn, trans, string.Concat(new object[] { "update wx_shop_category set class_list='", str2, "', class_layer=", num2, " where id=", num }));
                    this.UpdateChilds(conn, trans, num);
                }
            }
        }

        public void UpdateField(int id, string strValue)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("update wx_shop_category set " + strValue);
            builder.Append(" where id=" + id);
            DbHelperSQL.ExecuteSql(builder.ToString());
        }
    }
}

